﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using InsuranceSell.Code.Entities;
using System.Data.SqlClient;

namespace InsuranceSell.Code.Data
{
    class CustomerDAO : BaseDAO
    {
        public bool AddNewCustomer(Customer customer)
        {
            if (FindContractNo(customer.ContractNo)==null)
            {
                using (SqlConnection connection = GetConnection())
                {
                    SqlCommand command = new SqlCommand("sp_AddNewCustomer", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add(new SqlParameter("@CustomerName", customer.CustomerName));
                    command.Parameters.Add(new SqlParameter("@ContractNo", customer.ContractNo));
                    command.Parameters.Add(new SqlParameter("@StartDate", customer.StartDate));
                    command.Parameters.Add(new SqlParameter("@EndDate", customer.EndDate));
                    command.Parameters.Add(new SqlParameter("@Address", customer.Address));
                    command.Parameters.Add(new SqlParameter("@Telephone", customer.Telephone));
                    command.Parameters.Add(new SqlParameter("@Mobile", customer.Mobile));
                    command.Parameters.Add(new SqlParameter("@Email", customer.Email));
                    command.Parameters.Add(new SqlParameter("@TaxCode", customer.TaxCode));
                    command.Parameters.Add(new SqlParameter("@BankAccount", customer.BankAccount));
                    command.Parameters.Add(new SqlParameter("@BankName", customer.BankName));
                    command.Parameters.Add(new SqlParameter("@PresentativeName", customer.PresentativeName));
                    command.Parameters.Add(new SqlParameter("@PresentativeJobTitle", customer.PresentativeJobTitle));
                    command.Parameters.Add(new SqlParameter("@InsuranceType", customer.InsuranceType));
                    command.Parameters.Add(new SqlParameter("@InsuranceTarget", customer.InsuranceTarget));

                    try
                    {
                        connection.Open();
                        return (command.ExecuteNonQuery() != 0);
                    }
                    catch (Exception ex)
                    {
                        return false;
                    }
                }
            }
            else { return false; }
        }

        public DataTable LoadAllCustomer()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_LoadAllCustomer", connection);
                command.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
                DataTable allCustomers = new DataTable();

                try
                {
                    connection.Open();
                    sqlAdapter.Fill(allCustomers);
                    return allCustomers;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }
        public DataTable Load50NewCustomer()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_Load50NewCustomer", connection);
                command.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
                DataTable allCustomers = new DataTable();

                try
                {
                    connection.Open();
                    sqlAdapter.Fill(allCustomers);
                    return allCustomers;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }
        public DataTable FindContractNo(string ContractNo)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_FindContractNo", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ContractNo", ContractNo));
                SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
                DataTable aUser = new DataTable();

                try
                {
                    connection.Open();
                    sqlAdapter.Fill(aUser);
                    return aUser;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }
        public bool UpdateCustomer(Customer customer) {
            if (FindContractNo(customer.ContractNo)!=null)
            {
                using (SqlConnection connection = GetConnection())
                {
                    SqlCommand command = new SqlCommand("sp_UpdateCustomer", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add(new SqlParameter("@CustomerName", customer.CustomerName));
                    command.Parameters.Add(new SqlParameter("@ContractNo", customer.ContractNo));
                    command.Parameters.Add(new SqlParameter("@StartDate", customer.StartDate));
                    command.Parameters.Add(new SqlParameter("@EndDate", customer.EndDate));
                    command.Parameters.Add(new SqlParameter("@Address", customer.Address));
                    command.Parameters.Add(new SqlParameter("@Telephone", customer.Telephone));
                    command.Parameters.Add(new SqlParameter("@Mobile", customer.Mobile));
                    command.Parameters.Add(new SqlParameter("@Email", customer.Email));
                    command.Parameters.Add(new SqlParameter("@TaxCode", customer.TaxCode));
                    command.Parameters.Add(new SqlParameter("@BankAccount", customer.BankAccount));
                    command.Parameters.Add(new SqlParameter("@BankName", customer.BankName));
                    command.Parameters.Add(new SqlParameter("@PresentativeName", customer.PresentativeName));
                    command.Parameters.Add(new SqlParameter("@PresentativeJobTitle", customer.PresentativeJobTitle));
                    command.Parameters.Add(new SqlParameter("@InsuranceType", customer.InsuranceType));
                    command.Parameters.Add(new SqlParameter("@InsuranceTarget", customer.InsuranceTarget));

                    try
                    {
                        connection.Open();
                        return (command.ExecuteNonQuery() != 0);
                    }
                    catch (Exception ex)
                    {
                        return false;
                    }
                }
            }
            else { return false; }
        }
        public bool DeleteCustomer(string ContractNo) {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_DeleteCustomer", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ContractNo", ContractNo));
                try
                {
                    connection.Open();
                    return (command.ExecuteNonQuery() != 0);
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }
    }
}
